/**** Deletion of Database Specific Foreign Key Constraints begins ****/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_Question_Type_Category_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[Question_Type]
DROP CONSTRAINT FK_Question_Type_Category_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_Question_Type_Level_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[Question_Type]
DROP CONSTRAINT FK_Question_Type_Level_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_Question_Type_Image_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[Question_Type]
DROP CONSTRAINT FK_Question_Type_Image_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_Answer_Type_Question_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[Answer_Type]
DROP CONSTRAINT FK_Answer_Type_Question_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_Answer_Type_Image_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[Answer_Type]
DROP CONSTRAINT FK_Answer_Type_Image_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_Exam_Category_Level_Mapping_Exam_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[Exam_Category_Level_Mapping]
DROP CONSTRAINT FK_Exam_Category_Level_Mapping_Exam_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_Exam_Category_Level_Mapping_Category_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[Exam_Category_Level_Mapping]
DROP CONSTRAINT FK_Exam_Category_Level_Mapping_Category_Type
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[FK_Exam_Category_Level_Mapping_Level_Type]') AND OBJECTPROPERTY(id, N'IsConstraint') = 1)
ALTER TABLE [dbo].[Exam_Category_Level_Mapping]
DROP CONSTRAINT FK_Exam_Category_Level_Mapping_Level_Type
GO
/**** Deletion of Database Specific Foreign Key Constraints ends ****/
/**** Deletion of Database Tables begins ****/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[Question_Type]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Question_Type]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[Category_Type]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Category_Type]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[Level_Type]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Level_Type]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[Answer_Type]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Answer_Type]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[Exam_Type]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Exam_Type]
GO

IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE id = OBJECT_ID(N'[dbo].[Exam_Category_Level_Mapping]') 
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Exam_Category_Level_Mapping]
GO
/**** Deletion of Database Tables ends ****/
/**** Creation of Database tables begins ****/
CREATE TABLE [dbo].[Question_Type](
	[Question_TypeID] [KeyOrIDField] IDENTITY(1,1) NOT NULL,
	[Question_Statement] [BigDataField] NOT NULL,
	[Category_TypeID] [KeyOrIDField] NULL,
	[Level_TypeID] [KeyOrIDField] NULL,
	[Image_TypeID] [KeyOrIDField] NULL,
	[Status] [MasterSmallIDField] NOT NULL,
	[Status_Reason] [MediumTextField] NULL,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[Question_Type]') IS NOT NULL
	PRINT '<<< CREATED TABLE [Question_Type] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [Question_Type] >>>'
GO

CREATE TABLE [dbo].[Category_Type](
	[Category_TypeID] [KeyOrIDField] IDENTITY(1,1) NOT NULL,
	[Name] [NameField] NOT NULL,
	[Description] [DescriptionField] NULL,
	[Status] [MasterSmallIDField] NOT NULL ,
	[Status_Reason] [MediumTextField] NULL ,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[Category_Type]') IS NOT NULL
	PRINT '<<< CREATED TABLE [Category_Type] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [Category_Type] >>>'
GO

CREATE TABLE [dbo].[Level_Type](
	[Level_TypeID] [KeyOrIDField] IDENTITY(1,1) NOT NULL,
	[Name] [NameField] NOT NULL,
	[Description] [DescriptionField] NULL,
	[Status] [MasterSmallIDField] NOT NULL ,
	[Status_Reason] [MediumTextField] NULL ,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[Level_Type]') IS NOT NULL
	PRINT '<<< CREATED TABLE [Level_Type] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [Level_Type] >>>'
GO

CREATE TABLE [dbo].[Answer_Type](
	[Answer_TypeID] [KeyOrIDField] IDENTITY(1,1) NOT NULL,
	[Question_TypeID] [KeyOrIDField] NOT NULL,
	[Answer_Value] [BigTextField] NOT NULL,
	[Image_TypeID] [KeyOrIDField] NULL,
	[Is_Correct] [BooleanField] NOT NULL DEFAULT 0,
	[Status] [MasterSmallIDField] NOT NULL ,
	[Status_Reason] [MediumTextField] NULL ,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[Answer_Type]') IS NOT NULL
	PRINT '<<< CREATED TABLE [Answer_Type] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [Answer_Type] >>>'
GO

CREATE TABLE [dbo].[Exam_Type](
	[Exam_TypeID] [KeyOrIDField] IDENTITY(1,1) NOT NULL,
	[Name] [NameField] NOT NULL,
	[Description] [DescriptionField] NULL,
	[Time] [AmountField] NOT NULL,
	[Status] [MasterSmallIDField] NOT NULL ,
	[Status_Reason] [MediumTextField] NULL ,
	[Passing_Marks] [AmountField] NULL,
	[CreatedBy] [NameField] NOT NULL,
	[CreatedOn] [DateTimeField] NOT NULL DEFAULT GETDATE(),
	[ModifiedBy] [NameField] NULL,
	[ModifiedOn] [DateTimeField] NULL,
	[RFU1] [IdentifierField] NULL,
	[RFU2] [IdentifierField] NULL,
	[RFU3] [IdentifierField] NULL
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[Exam_Type]') IS NOT NULL
	PRINT '<<< CREATED TABLE [Exam_Type] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [Exam_Type] >>>'
GO

CREATE TABLE [dbo].[Exam_Category_Level_Mapping](
	[Exam_TypeID] [KeyOrIDField] NOT NULL,
	[Category_TypeID] [KeyOrIDField] NOT NULL,
	[Level_TypeID] [KeyOrIDField] NOT NULL,
	[Number_Questions] [AmountField] NOT NULL,
) ON [PRIMARY]
GO

IF OBJECT_ID(N'[dbo].[Exam_Category_Level_Mapping]') IS NOT NULL
	PRINT '<<< CREATED TABLE [Exam_Category_Level_Mapping] >>>'
ELSE
	PRINT '<<< FAILED CREATING TABLE [Exam_Category_Mapping] >>>'
GO
/**** Creation of Database tables ends ****/
/**** Creation of Database Primary keys begins ****/
ALTER TABLE [dbo].[Question_Type] WITH NOCHECK 
ADD CONSTRAINT [PK_Question_Type] PRIMARY KEY CLUSTERED (
	[Question_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Category_Type] WITH NOCHECK 
ADD CONSTRAINT [PK_Category_Type] PRIMARY KEY CLUSTERED (
	[Category_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Level_Type] WITH NOCHECK 
ADD CONSTRAINT [PK_Level_Type] PRIMARY KEY CLUSTERED (
	[Level_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Answer_Type] WITH NOCHECK 
ADD CONSTRAINT [PK_Answer_Type] PRIMARY KEY CLUSTERED (
	[Answer_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Exam_Type] WITH NOCHECK 
ADD CONSTRAINT [PK_Exam_Type] PRIMARY KEY CLUSTERED (
	[Exam_TypeID]
) ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Exam_Category_Level_Mapping] WITH NOCHECK 
ADD CONSTRAINT [PK_Exam_Category_Level_Mapping] PRIMARY KEY CLUSTERED (
	[Exam_TypeID],
	[Category_TypeID],
	[Level_TypeID]
) ON [PRIMARY] 
GO
/**** Creation of Database Primary keys ends ****/
/**** Creation of Database Foreign keys begins ****/
ALTER TABLE [dbo].[Question_Type] WITH NOCHECK 
ADD CONSTRAINT [FK_Question_Type_Category_Type] FOREIGN KEY (
	[Category_TypeID]
) REFERENCES [dbo].[Category_Type] (
	[Category_TypeID]
)
GO

ALTER TABLE [dbo].[Question_Type] WITH NOCHECK 
ADD CONSTRAINT [FK_Question_Type_Level_Type] FOREIGN KEY (
	[Level_TypeID]
) REFERENCES [dbo].[Level_Type] (
	[Level_TypeID]
)
GO

ALTER TABLE [dbo].[Question_Type] WITH NOCHECK 
ADD CONSTRAINT [FK_Question_Type_Image_Type] FOREIGN KEY (
	[Image_TypeID]
) REFERENCES [dbo].[Image_Type] (
	[Image_TypeID]
)
GO

ALTER TABLE [dbo].[Answer_Type] WITH NOCHECK 
ADD CONSTRAINT [FK_Answer_Type_Question_Type] FOREIGN KEY (
	[Question_TypeID]
) REFERENCES [dbo].[Question_Type] (
	[Question_TypeID]
)
GO

ALTER TABLE [dbo].[Answer_Type] WITH NOCHECK 
ADD CONSTRAINT [FK_Answer_Type_Image_Type] FOREIGN KEY (
	[Image_TypeID]
) REFERENCES [dbo].[Image_Type] (
	[Image_TypeID]
)
GO

ALTER TABLE [dbo].[Exam_Category_Level_Mapping] WITH NOCHECK 
ADD CONSTRAINT [FK_Exam_Category_Level_Mapping_Exam_Type] FOREIGN KEY (
	[Exam_TypeID]
) REFERENCES [dbo].[Exam_Type] (
	[Exam_TypeID]
)
GO

ALTER TABLE [dbo].[Exam_Category_Level_Mapping] WITH NOCHECK 
ADD CONSTRAINT [FK_Exam_Category_Level_Mapping_Category_Type] FOREIGN KEY (
	[Category_TypeID]
) REFERENCES [dbo].[Category_Type] (
	[Category_TypeID]
)
GO

ALTER TABLE [dbo].[Exam_Category_Level_Mapping] WITH NOCHECK 
ADD CONSTRAINT [FK_Exam_Category_Level_Mapping_Level_Type] FOREIGN KEY (
	[Level_TypeID]
) REFERENCES [dbo].[Level_Type] (
	[Level_TypeID]
)
GO
/**** Creation of Database Foreign keys ends ****/